package cn.lookup.dao.impl;

import cn.lookup.dao.UserDao;
import cn.lookup.domain.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

@Repository("userDao")
public class UserDaoImpl implements UserDao {
    @Autowired
    @Qualifier("jdbcTemplate")
    private JdbcTemplate jdbcTemplate;
    public List<User> getUserList() {
        String sql = "select * from sys_user";
        List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
        return userList;
    }

    public Long submitAdd(final User user) {
        final String sql = "insert into sys_user values(?,?,?,?,?)";
        PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                //自己组建一个PreParedStatement来执行sql
                PreparedStatement preparedStatement = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
                preparedStatement.setObject(1,null);
                preparedStatement.setString(2,user.getUsername());
                preparedStatement.setString(3,user.getEmail());
                preparedStatement.setString(4,user.getPassword());
                preparedStatement.setString(5,user.getPhoneNum());
                return preparedStatement;
            }
        };
        GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(preparedStatementCreator, generatedKeyHolder);
        long userId = generatedKeyHolder.getKey().longValue();
        //jdbcTemplate.update(sql,null,user.getUsername(),user.getEmail(),user.getPassword(),user.getPhoneNum());
        return userId;
    }
    //插入中间表关系
    public void submitRoleAndUserRelations(Long roleId, Long userId) {
        String sql="insert into sys_user_role values(?,?)";
        jdbcTemplate.update(sql,userId,roleId);
    }
    //删除中间表关系
    public void deleteRoleAndUserRelationship(Long userId) {
        String sql = "delete from sys_user_role where userId = ?";
        jdbcTemplate.update(sql,userId);
    }
    //删除用户表用户
    public void deleteUserById(Long userId) {
        String sql = "delete from sys_user where id = ?";
        jdbcTemplate.update(sql,userId);
    }
}
